package com.coldworks.coldjoke.manager;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.graphics.Bitmap;
import android.graphics.BitmapFactory;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.coldworks.base.manager.BasePrefManager;
import com.coldworks.coldjoke.model.JokeModel;

public class JokeDbManager {
	protected final Logger log = LoggerFactory.getLogger(this.getClass());
	private static JokeDbManager instance = null;
	private static int DB_VERSION = 3;
	public static String Lock = "dblock";
	private SQLiteDatabase mdb;
	private JokeDbHelper jokeDbHelper;

	public static JokeDbManager getInstance() {
		if (instance == null) {
			synchronized (JokeDbManager.class) {
				if (instance == null) {
					instance = new JokeDbManager();
				}
			}
		}
		return instance;
	}

	private JokeDbManager() {
	}

	public boolean openJokeDb(Context context) {
		boolean res = false;
		if (mdb != null) {
			res = mdb.isOpen();
		}
		if (!res) {
			try {
				jokeDbHelper = new JokeDbHelper(context, DBCONST.DB_NAME, null,
						DB_VERSION);
				// log.info("dbHelper opened: " + jokeDbHelper);
				mdb = jokeDbHelper.getWritableDatabase();
				if (mdb == null) {
					jokeDbHelper.getReadableDatabase();
				}
				// log.info("db opened: " + mdb);
				res = (mdb != null) && mdb.isOpen();
			} catch (Exception e) {
				log.error("", e);
			}
		}
		if (!res) {
			log.info("未成功打开数据库");
		}
		return res;
	}

	public void closeJokeDb() {
		if (mdb != null && mdb.isOpen()) {
			mdb.close();
			// log.info("db closed: " + mdb);
		} else {
			log.info("nothing");
		}
	}

	public boolean createJokeDb(Context context, String tableName) {
		if (!openJokeDb(context)) {
			log.info("未成功打开数据库");
			return false;
		}
		if (BasePrefManager.getInstance().getIntFromPrefs(context,
				"TABLE_" + tableName, 0) != DB_VERSION) {
			mdb.execSQL("DROP TABLE IF EXISTS " + tableName);
		}
		String sql = "CREATE TABLE IF NOT EXISTS "
				+ tableName
				+ " (joke_id TEXT PRIMARY KEY, joke_index INTEGER, joke_time TEXT, joke_text TEXT, "
				+ "joke_img_url TEXT, joke_img_path TEXT, joke_img_thumbnail BLOB, good_num INTEGER, "
				+ "bad_num INTEGER, reply_num INTEGER, user_id INTEGER, user_name TEXT, user_icon_url TEXT, joke_read INTEGER, "
				+ "joke_liked INTEGER, joke_disliked INTEGER)";
		mdb.execSQL(sql);
		BasePrefManager.getInstance().setIntToPrefs(context,
				"TABLE_" + tableName, DB_VERSION);
		return true;
	}

	/**
	 * 保存一个笑话
	 * 
	 * @param context
	 * @param tableName
	 * @param jokeInfo
	 * @return
	 */
	public boolean saveOneJoke(Context context, String tableName,
			JokeModel jokeInfo) {
		synchronized (Lock) {
			if (!openJokeDb(context)) {
				log.info("未成功打开数据库");
				return false;
			}
			ByteArrayOutputStream baos = null;
			try {
				ContentValues values = new ContentValues();
				values = new ContentValues();
				values.put(DBCONST.JOKE_ID, jokeInfo.getJokeId());
				values.put(DBCONST.JOKE_INDEX, jokeInfo.getJokeIndex());
				values.put(DBCONST.JOKE_TIME, jokeInfo.getTime());
				values.put(DBCONST.JOKE_TEXT, jokeInfo.getJokeText());
				values.put(DBCONST.JOKE_IMG_URL, jokeInfo.getImgUrl());
				values.put(DBCONST.JOKE_IMG_PATH, jokeInfo.getImgPath());
				if (jokeInfo.hasImage()) {
					baos = new ByteArrayOutputStream();
					jokeInfo.getThumb().compress(Bitmap.CompressFormat.JPEG,
							100, baos);
					byte[] thumbBytes = baos.toByteArray();
					values.put(DBCONST.JOKE_IMG_THUMBNAIL, thumbBytes);
				} else {
					// values.put(DBCONST.JOKE_IMG_THUMBNAIL, thumbBytes);
				}
				values.put(DBCONST.GOOD_NUM, jokeInfo.getGoodNum());
				values.put(DBCONST.BAD_NUM, jokeInfo.getBadNum());
				values.put(DBCONST.REPLY_NUM, jokeInfo.getReplyNum());
				values.put(DBCONST.USER_ID, jokeInfo.getUserId());
				values.put(DBCONST.USER_NAME, jokeInfo.getUserName());
				values.put(DBCONST.USER_ICON_URL, jokeInfo.getUserIcon());
				values.put(DBCONST.JOKE_READ, jokeInfo.getRead());
				values.put(DBCONST.JOKE_LIKED, jokeInfo.getLiked());
				values.put(DBCONST.JOKE_DISLIKED, jokeInfo.getDisliked());
				mdb.insert(tableName, null, values);
				return true;
			} catch (Exception e) {
				log.error("", e);
				return false;
			} finally {
				try {
					if (baos != null) {
						baos.close();
					}
				} catch (IOException e) {
					log.error("", e);
				}
				closeJokeDb();
			}
		}
	}

	/**
	 *  查询并返回一个笑话
	 * 
	 * @param context
	 * @param tableName
	 * @param jokeId
	 * @return JokeInfo
	 */
	public JokeModel getOneJokeFull(Context context, String tableName,
			String jokeId) {
		synchronized (Lock) {
			if (!openJokeDb(context)) {
				log.info("未成功打开数据库");
				return null;
			}
			Cursor cursor = mdb.query(tableName, new String[] {
					DBCONST.JOKE_ID, DBCONST.JOKE_INDEX, DBCONST.JOKE_TIME,
					DBCONST.JOKE_TEXT, DBCONST.JOKE_IMG_URL,
					DBCONST.JOKE_IMG_PATH, DBCONST.JOKE_IMG_THUMBNAIL,
					DBCONST.GOOD_NUM, DBCONST.BAD_NUM, DBCONST.REPLY_NUM,
					DBCONST.USER_ID, DBCONST.USER_NAME, DBCONST.USER_ICON_URL,
					DBCONST.JOKE_READ, DBCONST.JOKE_LIKED,
					DBCONST.JOKE_DISLIKED }, DBCONST.JOKE_ID + "=?",
					new String[] { jokeId }, null, null, null, null);
			// 取一则笑话
			cursor.moveToFirst();
			JokeModel jokeInfo = new JokeModel();
			jokeInfo.setJokeId(cursor.getString(0));
			jokeInfo.setJokeIndex(cursor.getInt(1));
			jokeInfo.setTime(cursor.getString(2));
			jokeInfo.setJokeText(cursor.getString(3));
			jokeInfo.setImgUrl(cursor.getString(4));
			jokeInfo.setImgPath(cursor.getString(5));
			try {
				byte[] b = cursor.getBlob(6);
				if (b != null) {
					jokeInfo.setThumb(BitmapFactory.decodeByteArray(b, 0,
							b.length));
				}
			} catch (Exception e) {
				log.error("", e);
			}// jokeInfo.setThumb(cursor.getBlob(6));
			jokeInfo.setGoodNum(cursor.getInt(7));
			jokeInfo.setBadNum(cursor.getInt(8));
			jokeInfo.setReplyNum(cursor.getInt(9));
			jokeInfo.setUserId(cursor.getString(10));
			jokeInfo.setUserName(cursor.getString(11));
			jokeInfo.setUserIcon(cursor.getString(12));
			jokeInfo.setRead(cursor.getInt(13) != 0);
			jokeInfo.setLiked(cursor.getInt(14));
			jokeInfo.setDisliked(cursor.getInt(15));
			closeJokeDb();
			return jokeInfo;
		}
	}

	/**
	 * 更新一个笑话的数据
	 * 
	 * @param context
	 * @param tableName
	 * @param jokeId
	 * @param goodNum
	 * @param badNum
	 * @param replyNum
	 * @param read
	 * @param liked
	 * @param disliked
	 */
	public boolean updateOneJoke(Context context, String tableName,
			JokeModel joke) {
		synchronized (Lock) {
			if (!openJokeDb(context)) {
				log.info("未成功打开数据库");
				return false;
			}
			ContentValues values = new ContentValues();
			values = new ContentValues();
			values.put(DBCONST.JOKE_INDEX, joke.getJokeIndex());
			values.put(DBCONST.GOOD_NUM, joke.getGoodNum());
			values.put(DBCONST.BAD_NUM, joke.getBadNum());
			values.put(DBCONST.REPLY_NUM, joke.getReplyNum());
			values.put(DBCONST.JOKE_READ, joke.getRead());
			values.put(DBCONST.JOKE_LIKED, joke.getLiked());
			values.put(DBCONST.JOKE_DISLIKED, joke.getDisliked());
			mdb.update(tableName, values, DBCONST.JOKE_ID + "=?",
					new String[] { joke.getJokeId() });
			closeJokeDb();
			return true;
		}
	}

	/**
	 * 批量删除笑话
	 * 
	 * @param context
	 * @param tableName
	 * @param list
	 * @param remainNum
	 */
	public boolean deleteJokes(Context context, String tableName,
			List<String> list) {
		synchronized (Lock) {
			if (!openJokeDb(context)) {
				log.info("未成功打开数据库");
				return false;
			}
			if (list == null || list.size() == 0) {
				log.info("delete nothing");
				return true;
			}

			for (int i = 0; i < list.size(); i++) {
				Cursor cursor = mdb.query(tableName, new String[] {
						DBCONST.JOKE_ID, DBCONST.JOKE_IMG_URL,
						DBCONST.JOKE_IMG_PATH, }, DBCONST.JOKE_ID + "=?",
						new String[] { list.get(i) }, null, null, null, null);
				cursor.moveToFirst();
				String imgUrl = cursor.getString(1);
				String imgPath = cursor.getString(2);
				if (imgUrl.equals("") || imgPath.equals("")) {
					// 无图片
				} else {
					try {
						File imgFile = new File(imgPath);
						if (imgFile != null && imgFile.exists()) {
							imgFile.delete();
						}
					} catch (Exception e) {
						log.warn("", e);
					}
				}
				mdb.delete(tableName, DBCONST.JOKE_ID + "=?",
						new String[] { list.get(i) });
			}
			closeJokeDb();
			return true;
		}
	}

	public List<JokeModel> getAllJokeSummary(Context context, String tableName) {
		synchronized (Lock) {
			List<JokeModel> list = new ArrayList<JokeModel>();
			if (!openJokeDb(context)) {
				return list;
			}
			Cursor cursor = mdb.query(tableName, new String[] {
					DBCONST.JOKE_ID, DBCONST.JOKE_INDEX, DBCONST.JOKE_TIME,
					DBCONST.JOKE_TEXT, DBCONST.JOKE_IMG_URL,
					DBCONST.JOKE_IMG_PATH, DBCONST.JOKE_IMG_THUMBNAIL,
					DBCONST.GOOD_NUM, DBCONST.BAD_NUM, DBCONST.REPLY_NUM,
					DBCONST.USER_ID, DBCONST.USER_NAME, DBCONST.USER_ICON_URL,
					DBCONST.JOKE_READ, DBCONST.JOKE_LIKED,
					DBCONST.JOKE_DISLIKED }, null, null, null, null,
					DBCONST.JOKE_INDEX);

			JokeModel jokeInfo;
			while (cursor.moveToNext()) {
				jokeInfo = new JokeModel();
				jokeInfo.setJokeId(cursor.getString(0));
				jokeInfo.setJokeIndex(cursor.getInt(1));
				// log.info(jokeInfo.getJokeId() + " - " +
				// jokeInfo.getJokeIndex());
				jokeInfo.setTime(cursor.getString(2));
				jokeInfo.setJokeText(cursor.getString(3));
				jokeInfo.setImgUrl(cursor.getString(4));
				jokeInfo.setImgPath(cursor.getString(5));
				try {
					byte[] b = cursor.getBlob(6);
					if (b != null) {
						jokeInfo.setThumb(BitmapFactory.decodeByteArray(b, 0,
								b.length));
					}

				} catch (Exception e) {
					log.error("", e);
				}
				// jokeInfo.setThumb(cursor.getBlob(5));
				jokeInfo.setGoodNum(cursor.getInt(7));
				jokeInfo.setBadNum(cursor.getInt(8));
				jokeInfo.setReplyNum(cursor.getInt(9));
				jokeInfo.setUserId(cursor.getString(10));
				jokeInfo.setUserName(cursor.getString(11));
				jokeInfo.setUserIcon(cursor.getString(12));
				jokeInfo.setRead(cursor.getInt(13) != 0);
				jokeInfo.setLiked(cursor.getInt(14));
				jokeInfo.setDisliked(cursor.getInt(15));
				list.add(jokeInfo);
			}
			cursor.close();
			closeJokeDb();
			return list;
		}
	}

	public boolean removeLikeState(Context context, String tableName) {
		synchronized (Lock) {
			if (!openJokeDb(context)) {
				log.info("未成功打开数据库");
				return false;
			}
			ContentValues values = new ContentValues();
			values = new ContentValues();
			values.put(DBCONST.JOKE_LIKED, -1);
			values.put(DBCONST.JOKE_DISLIKED, -1);
			mdb.update(tableName, values, null, null);
			closeJokeDb();
			return true;
		}
	}

	class JokeDbHelper extends SQLiteOpenHelper {
		protected final Logger log = LoggerFactory.getLogger(this.getClass());

		public JokeDbHelper(Context context, String name,
				CursorFactory factory, int version) {
			super(context, name, factory, version);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			log.info("onCreate");
		}

		// 更新表
		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			log.info("onUpgrade");
		}
	}

}
